package com.itxh.mybatis.dao;

import com.itxh.mybatis.po.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * 持久层代码--使用JDBC实现
 * @author think
 *
 */
public class UserDaoImplWithJdbc implements UserDao {

	@Override
	public List<User> queryUserByName(String username) {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		List<User> users = new ArrayList<>();
		try {
			// 加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");

			// 通过驱动管理类获取数据库链接connection = DriverManager
			connection = DriverManager.getConnection("jdbc:mysql://124.221.171.208:3306/itxuhang?characterEncoding=utf-8",
					"root", "itxuhang");

			// 定义sql语句 ?表示占位符
			//String sql = "select * from user where id = #{id} and username = #{username}";
			String sql = "select * from user where username like '%"+username+"%'";
			// 获取 statement
			statement = connection.createStatement();

			// 向数据库发出 sql 执行查询，查询出结果集
			rs = statement.executeQuery(sql);

			// 遍历查询结果集
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));

				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 释放资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
				}
			}
		}
		return users;
	}

	@Override
	public User queryUserById(Integer id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		ResultSet rs = null;

		List<User> users = new ArrayList<>();
		try {
			// 1、加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");

			// 2、通过驱动管理类获取数据库链接connection = DriverManager
			connection = DriverManager.getConnection("jdbc:mysql://124.221.171.208:3306/itxuhang?characterEncoding=utf-8",
					"root", "itxuhang");

			// 3、定义sql语句 ?表示占位符
			String sql = "select * from user where id = ?";

			// 4、获取预处理 statement
			preparedStatement = connection.prepareStatement(sql);

			// 5、设置参数，第一个参数为 sql 语句中参数的序号（从 1 开始），第二个参数为设置的
			preparedStatement.setInt(1, id);

			// 6、向数据库发出 sql 执行查询，查询出结果集
			rs = preparedStatement.executeQuery();

			// 7、遍历查询结果集
			User user = null;
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));

				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 8、释放资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
				}
			}
		}
		return users.get(0);
	}

}
